﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using MySql.Data;
namespace PermintaanBarangJasa
{
    public partial class FormPermintaanBarangJasa : Form
    {
        private string database = "adsi";
        private string server = "localhost";
        private string uid = "root";
        private string password = "";
        public DataTable table;
        string MyConnectionString;
        private MySqlConnection connection;
        private MySqlCommand cmd;
        private MySqlDataAdapter adapter;
        private MySqlDataReader reader;
        private string b3nonb3;
        public FormPermintaanBarangJasa()
        {
            InitializeComponent();
            MyConnectionString = "SERVER=" + server + ";" + "Database=" + database +
           ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
            connection = new MySqlConnection(MyConnectionString);

            //create table
            table = new DataTable();
            table.Columns.Add("nama_brg_jasa", typeof(string));
            table.Columns.Add("spesifikasi", typeof(string));
            table.Columns.Add("jumlah", typeof(int));
            table.Columns.Add("satuan", typeof(string));
            table.Columns.Add("perkiraanHargaSatuan", typeof(double));
            table.Columns.Add("perkiraanJumlahHarga", typeof(double));
            table.Columns.Add("keterangan", typeof(string));
        }

        private void buttonDiperlukan_Click(object sender, EventArgs e)
        {
            FormRecordKeperluan frmRecordKeperluan = new FormRecordKeperluan();
            frmRecordKeperluan.ShowDialog();
        }

        private void FormPermintaanBarangJasa_Load(object sender, EventArgs e)
        {
            textNoPermintaan.Text = DateTime.Now.ToString("yyyyMMddHHmmss");
            textTanggalPermintaan.Text = DateTime.Now.ToString("dd-MM-yyyy");

            connection.Open();
            string query = "SELECT dep_pengaju FROM tbl_spesifikasi_pemohon GROUP BY dep_pengaju";
            adapter = new MySqlDataAdapter(query, connection);
            DataTable tabel2 = new DataTable();
            adapter.Fill(tabel2);
            for (int i = 0; i < tabel2.Rows.Count; i++)
            {
                comboDariDept.Items.Add(tabel2.Rows[i]["dep_pengaju"].ToString());
                
                
            }
            connection.Close();
            connection.Open();
            string query2 = "SELECT dep_pengaju FROM tbl_spesifikasi_pemohon GROUP BY dep_pengaju";
            adapter = new MySqlDataAdapter(query2, connection);
            DataTable tabel3 = new DataTable();
            adapter.Fill(tabel3);
            for (int i = 0; i < tabel3.Rows.Count; i++)
            {
                comboDariDept.Items.Add(tabel3.Rows[i]["dep_pengaju"].ToString());


            }
            connection.Close();


        }

        private void ButtonAdd_Click(object sender, EventArgs e)
        {
            if (textBoxNamaBarangJasa.Text == "")
            {
                MessageBox.Show("Isi dahulu text nama barang");
                textBoxNamaBarangJasa.Focus();
            }
            else if (textBoxSpesifikasi.Text == "")
            {
                MessageBox.Show("Isi dahulu text spesifikasi");
                textBoxSpesifikasi.Focus();
            }

           table.Rows.Add(textBoxNamaBarangJasa.Text, textBoxSpesifikasi.Text.Trim(), textBoxJumlah.Text.Trim(), comboBoxSatuan.Text.Trim(), textHargaSatuan.Text.Trim(),textJumlahHarga.Text.Trim(), richKeterangan.Text.Trim());
           dataGridView1.DataSource = table;
           clearText();
        }

        private void clearText()
        {
            textBoxNamaBarangJasa.Clear();
            textBoxJumlah.Clear();
            textBoxSpesifikasi.Clear();
            textJumlahHarga.Clear();
            textHargaSatuan.Clear();
            richKeterangan.Clear();
            comboBoxSatuan.Text = "";

        }
        private void excuteInsertQuery(MySqlCommand cmd)
        {
            int queryResult = cmd.ExecuteNonQuery();
            if (queryResult > 0)
                MessageBox.Show("Berhasil Insert");
            else
                MessageBox.Show("Gagal Insert");
        }
        private void button1_Click(object sender, EventArgs e)
        {
            clearText();
        }
        private void saveSpesifikasiPemohon()
        {
            connection.Open();
            string query = "INSERT INTO tbl_spesifikasi_pemohon (tgl_permintaan, dep_pengaju, nama_pengaju, tgl_diperlukan_dari, tgl_diperlukan_sampai) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + comboDariDept.Text.Trim() + "','" + textNamaPengaju.Text.Trim() + "','" + Convert.ToDateTime(dateTimeTglDiperlukanDari.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(dateTimeTglDiperlukanSampai.Text).ToString("yyyy-MM-dd") + "')";
            cmd = new MySqlCommand(query, connection);
            excuteInsertQuery(cmd);
            connection.Close();
        }
        private void saveSpesifikasiBrg()
        {
            //get data from table
            foreach(DataRow drBahan in table.Rows)
            {
                string nama_brg_jasa = drBahan[0].ToString();
                string spesifikasi = drBahan[1].ToString();
                string jumlah = drBahan[2].ToString();
                string satuan = drBahan[3].ToString();
                string perkiraanHargaSatuan = drBahan[4].ToString();
                string perkiraanJumlahHarga = drBahan[5].ToString();
                string keterangan = drBahan[6].ToString();

                connection.Open();
                string query = "INSERT INTO tbl_spesifikasi_brg(no_permintaan,nama_barang_jasa, spesifikasi, jumlah,satuan, perkiraan_harga_satuan, jumlah_harga, b3_nonb3, keterangan) SELECT no_permintaan ,'" + nama_brg_jasa + "', '" + spesifikasi + "','" + jumlah + "','" + satuan + "','" + perkiraanHargaSatuan + "','" + perkiraanJumlahHarga + "','" + b3nonb3 + "','" + keterangan + "' FROM tbl_spesifikasi_pemohon WHERE no_permintaan = (SELECT MAX(no_permintaan) FROM tbl_spesifikasi_pemohon)";
                cmd = new MySqlCommand(query, connection);
                excuteInsertQuery(cmd);
                connection.Close();
            }
        }
        private void buttonSave_Click(object sender, EventArgs e)
        {
            saveSpesifikasiPemohon();
            saveSpesifikasiBrg();
  
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            b3nonb3 = radioB3.Text;
        }

        private void radioNonB3_CheckedChanged(object sender, EventArgs e)
        {
            b3nonb3 = radioNonB3.Text;
        }
    }
}
